﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using eCommerce.core;
using eCommerce.db.DataRespository;
using eCommerce.db.SQLDataAccess;

namespace eCommerce.db.DALRespository
{
    public class OrderDal
    {
        #region OrderDal private
        private static void SetInfo(IDataRecord reader, ref OrderEntity o)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                PropertyInfo propertyInfo = DbCommon.GetPropertyInfo(o, reader.GetName(i));
                if (((propertyInfo != null) && (reader[i] != null)) && (reader[i] != DBNull.Value))
                    propertyInfo.SetValue(o, reader[i], null);
                else
                    o[reader.GetName(i)] = reader[i];
            }
        }

        private static void SetInfoList(ref SqlDataReader reader, ref List<OrderEntity> orderList)
        {
            while (reader.Read())
            {
                var o = new OrderEntity();
                SetInfo(reader, ref o);
                orderList.Add(o);
            }
            reader.Dispose();
        }      
        #endregion

        public static List<OrderEntity> Search(string keyword, int status, int storeId, int adminUserId, 
                                               string startDate, string endDate, int pageIndex, int pageSize, ref int totalRecords)
        {                       
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstOrder = new List<OrderEntity>();
            SqlDataReader reader = null;
            var connection = new SqlConnection(DbCommon.ConnectionString);

            try
            {
                connection.Open();
                if (connection.State == ConnectionState.Open)
                {
                    var command = new SqlCommand();
                    DbCommon.AddParameter(command, "Keyword", keyword);
                    DbCommon.AddParameter(command, "Status", status);
                    DbCommon.AddParameter(command, "StoreId", storeId);
                    DbCommon.AddParameter(command, "AdminUserId", adminUserId);
                    DbCommon.AddParameter(command, "StartDate", startDate);
                    DbCommon.AddParameter(command, "EndDate", endDate);
                    DbCommon.AddParameter(command, "PageIndex", pageIndex);
                    DbCommon.AddParameter(command, "PageSize", pageSize);                                        

                    command.Connection = connection;
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = DbCommon.DatabaseSchema + "Order_Filter";

                    reader = command.ExecuteReader();
                    SetInfoList(ref reader, ref lstOrder);

                    totalRecords = SearchCount(keyword, status, storeId, adminUserId, startDate, endDate);
                    reader.Close();
                }

                return lstOrder;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Order_Filter: {0}", ex.Message));
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                connection.Close();
                connection.Dispose();
            }    
        }

        public static int SearchCount(string keyword, int status, int storeId, int adminUserId, string startDate, string endDate)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            return (int)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, "Order_Filter_Count", keyword, status, storeId, adminUserId, startDate, endDate);            
        }

        public static DataTable GetByStatus(int status)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {
                return SqlHelper.ExecuteDataset(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Order_GetByStatus", status).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Order_GetByStatus: {0}", ex.Message));
            }
        }

        public static DataTable GetByCustomerId(int customerId)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {
                return SqlHelper.ExecuteDataset(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Order_GetByCustomerId", customerId).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Order_GetByCustomerId: {0}", ex.Message));
            }
        }
        
        public static DataTable GetListAdminUser(int storeId)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {
                return SqlHelper.ExecuteDataset(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Order_GetListAdminUser", storeId).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Order_GetListAdminUser: {0}", ex.Message));
            }
        }

        public static DataTable GetListStore(int storeId)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {
                return SqlHelper.ExecuteDataset(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Order_GetListStore", storeId).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Order_GetListStore: {0}", ex.Message));
            }
        }

        public static OrderEntity GetById(int id)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var o = new OrderEntity();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Order_GetById", id);
                if (reader.Read())
                    SetInfo(reader, ref o);
                return o;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Order_GetById: {0}", ex.Message));
            }
        }

        public static int Create(OrderEntity o)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            return (int)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, "Order_Create",
                                    o.AdminUserId, o.CustomerId, o.StoreId, o.Status, o.LiquidationType, o.TotalValue,
                                    o.Discount, o.Percent, o.Freight,
                                    o.OrderDate, o.RequiredDate, o.Note);            
        }

        public static void Delete(int id)
        {
            SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, "Order_Delete", id);
        }

        public static void UpdateStatus(int id, int status)
        {
            SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, "Order_Update_Status", id, status);
        }

        public static void UpdateNote(int id, string note)
        {
            SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, "Order_Update_Note", id, note);
        }

        public static int GetStatistic(int status)
        {
            return (int)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, "Order_GetStatistic", status);
        }

        public static int GetTotalOrderByCustomerId(int customerId, int status)
        {
            try
            {
                return (int)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, "Order_GetTotalOrderByCustomerId", customerId, status);
            }
            catch { return 0; }            
        }

        public static decimal GetTotalMoneyByCustomerId(int customerId, int status)
        {
            try
            {
                return (decimal)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, "Order_GetTotalMoneyByCustomerId", customerId, status);
            }
            catch { return 0; }   
        }
    }
}
